﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.Xml;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint.Utilities;
using Microsoft.SharePoint.WebControls;

public partial class Trade_OneDay_Sum : UserControl
{

    #region Members

    protected string sql = "";
    protected string _sql =
@"
SELECT 
магазин AS Магазин
, SUM([Сумма реализации] - [Сумма возвратов] + [Сумма оптовой реализации]) AS Сумма
FROM vw_a_Trade_Progress
GROUP BY дата, магазин
HAVING (дата = '{0,4:0000}-{1,2:00}-{2,2:00} 00:00:00')
ORDER BY Магазин";
    
    protected string sqlAppr = "";
    protected string _sqlAppr =
@"
SELECT D_Confirm AS Дата_утверждения, ShopName AS Магазин, Amount AS Сумма, Approver_Name AS Утверждающий
FROM dbo.MoneyConfirm
WHERE (D = '{0,4:0000}-{1,2:00}-{2,2:00} 00:00:00') 
ORDER BY D_Confirm DESC
";

    #endregion Members

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            DateTime dtNow = DateTime.Now;
            oDT.SelectedDate = 
                (new DateTime(dtNow.Year, dtNow.Month, dtNow.Day)) - (new TimeSpan(24, 0, 0));
            ViewState["_LastDT"] = oDT.SelectedDate;
        }

        oInfo.Text = "";
        oInfo.Text += "";

        sql = string.Format(_sql, oDT.SelectedDate.Year, oDT.SelectedDate.Month, oDT.SelectedDate.Day);
        SqlDS.SelectCommand = sql;

        sqlAppr = string.Format(_sqlAppr, oDT.SelectedDate.Year, oDT.SelectedDate.Month, oDT.SelectedDate.Day);
        SqlDSApprovers.SelectCommand = sqlAppr;

        if ((DateTime)ViewState["_LastDT"] != oDT.SelectedDate)
        {
            gw.SelectedIndex = -1;
            mv.ActiveViewIndex = 0;
        }

        DataBind();

        ViewState["_LastDT"] = oDT.SelectedDate;

        //Response.Write(sql);
    }

    void MyRefresh()
    {
        PanelRed.Visible = false;
        PanelGreen.Visible = false;

        if (ViewState["_Amount"] == null)
        {
            return;
        }

        Decimal amount = (Decimal)ViewState["_Amount"];

        if (gvAppr.Rows.Count > 0)
        {
            PanelGreen.Visible = true;
            string str = SPEncode.HtmlDecode(gvAppr.Rows[0].Cells[2].Text);
            str = str.Replace("р.", "");
            str = str.Replace(" ", "");
            Decimal d = Convert.ToDecimal(str);
            if (amount != d)
            {
                PanelRed.Visible = true;
            }
        }
        else
        {
            PanelRed.Visible = true;
        }
    }

    #region Common
    #region App Settings
    static public string GetAppSetting(String mName)
    {
        String vStr = null;
        SPWebService mSer = SPWebService.ContentService;
        #region Ver 1
        if (mSer != null)
        {
            foreach (SPWebConfigModification mMod in mSer.WebConfigModifications)
            {
                if (mMod.Owner == mName)
                    vStr = mMod.Value;
            }
        }
        if (vStr != null)
        {
            vStr =
                vStr.Substring(
                    vStr.IndexOf("value=") + 7
                    , vStr.Length - 7 - vStr.IndexOf("value=") - 4);
        }
        #endregion Ver 1
        else
        #region Ver 2
        {
            string strPath =
@"C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config";
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(strPath);
            XmlNode node = xmlDoc.SelectSingleNode(@"/configuration/appSettings");
            foreach (XmlNode keynode in node.ChildNodes)
            {
                string keyStr = keynode.Attributes["key"].Value;
                if (keyStr == mName)
                {
                    vStr = keynode.Attributes["value"].Value;
                    break;
                }
            }
        }
        #endregion Ver 2
        if (String.IsNullOrEmpty(vStr))
            vStr = "";
        return vStr;
    }
    #endregion App Settings
    #endregion

    #region Page Events

    protected void oDT_OnDateChanged(object sender, EventArgs e)
    {
        gw.SelectedIndex = -1;
        mv.ActiveViewIndex = 0;
    }
    
    protected void gw_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
    {
        GridViewRow row = gw.Rows[((GridViewSelectEventArgs)e).NewSelectedIndex];
    }

    protected void gw_SelectedIndexChanged(object sender, EventArgs e)
    {
        GridViewRow row = gw.SelectedRow;
        if (row != null)
            RefreshDetals(row.Cells[1].Text, oDT.SelectedDate);
    }

    #endregion Page Events

    #region Click

    protected void btnApprove_Click(object sender, EventArgs e)
    {
        mv.ActiveViewIndex = 1;
    }

    protected void btnAppr_Click(object sender, EventArgs e)
    {
        DBApprovIns(
            "Все магазины"
            , (Decimal)ViewState["_Amount"]
            , SPContext.Current.Web.CurrentUser.LoginName
            , SPContext.Current.Web.CurrentUser.Name);
        mv.ActiveViewIndex = 0;
        MyRefresh();
    }

    protected void btnCancel_Click(object sender, EventArgs e)
    {
        mv.ActiveViewIndex = 0;
    }

    #endregion Click

    #region Data

    protected void gw_DataBound(object sender, EventArgs e)
    {
        if (gw.SelectedIndex >= 0)
            RefreshDetals(gw.Rows[gw.SelectedIndex].Cells[1].Text, oDT.SelectedDate);
        else
            RefreshDetals("", oDT.SelectedDate);

        Decimal amount = 0;
        foreach (System.Web.UI.WebControls.TableRow r in gw.Rows)
        {
            string str = SPEncode.HtmlDecode(r.Cells[2].Text);
            str = str.Replace("р.", "");
            str = str.Replace(" ", "");
            Decimal d = Convert.ToDecimal(str);
            amount += d;
        }
        lblAmount.Text = "Итого - " + amount.ToString("C");
        ViewState["_Amount"] = amount;
        lblConfirmText.Text = "Утверждаемая сумма - " + amount.ToString("C");

        MyRefresh();
    }

    private void RefreshDetals(string strShopName, DateTime parDT)
    {

        if (string.IsNullOrEmpty(strShopName) || parDT == null)
        {
            lblShopName.Text = "";
            oInfo.Text = "";
            return;
        }

        DateTime dt = DateTime.Now;
        int year = dt.Year;
        int month = dt.Month;

        string sql =
@"SELECT
  секция AS Ссекция
, SUM([Сумма реализации]) AS 'Сумма реализации'
, SUM([Сумма проданного товара в закупочных ценах]) AS 'Сумма проданного товара в закупочных ценах'
, SUM([Сумма возвратов в закупочных ценах]) AS 'Сумма возвратов в закупочных ценах'
, SUM([Сумма возвратов]) AS 'Сумма возвратов'
, SUM([Сумма оптовой реализации в закупочных ценах]) AS 'Сумма оптовой реализации в закупочных ценах'
, SUM([Сумма оптовой реализации]) AS 'Сумма оптовой реализации'
FROM vw_a_Trade_Progress
GROUP BY магазин, секция, дата
HAVING (дата = ?) AND (магазин = ?)
";

        string connStr = SqlDS.ConnectionString;

        Decimal val1 = 0, val2 = 0, val3 = 0, val4 = 0, val5 = 0, val6 = 0;

        try
        {
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();
                using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                {
                    cmd.Parameters.Add("@parDT", OleDbType.DBDate).Value = parDT;
                    cmd.Parameters.Add("@shopname", OleDbType.VarChar).Value = strShopName;

                    OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
                    DataTable tbl = new DataTable();
                    ad.Fill(tbl);

                    StringBuilder sb = new StringBuilder();
                    int cnt = 0;

                    foreach (DataRow r in tbl.Rows)
                    {
                        cnt++;
                        sb.Append(
    @"</br> <table border=1>
    <tr>
        <td style='background-color: #505050; color: #00FF00; font-size: large'>
            " + (string)r[0] +
    @"</td>
    <td style='background-color: #505050; color: #00FF00; font-size: large'>
        Сумма</td></tr>");

                        if (r[1] != DBNull.Value)
                        {
                            val1 += (Decimal)r[1];
                            sb.Append(@"<tr>
<td>&nbsp; Сумма реализации</td><td>&nbsp;" + ((Decimal)r[1]).ToString("C") +
@"</td></tr>");
                        }
                        if (r[2] != DBNull.Value)
                        {
                            val2 += (Decimal)r[2];
                            sb.Append(@"<tr>
<td>&nbsp; Сумма проданного товара в закупочных ценах</td><td>&nbsp;" + ((Decimal)r[2]).ToString("C") +
@"</td></tr>");
                        }
                        if (r[3] != DBNull.Value)
                        {
                            val3 += (Decimal)r[3];
                            sb.Append(@"<tr>
<td>&nbsp; Сумма возвратов в закупочных ценах</td><td>&nbsp;" + ((Decimal)r[3]).ToString("C") +
@"</td></tr>");
                        }
                        if (r[4] != DBNull.Value)
                        {
                            val4 += (Decimal)r[4];
                            sb.Append(@"<tr>
<td>&nbsp; Сумма возвратов</td><td>&nbsp;" + ((Decimal)r[4]).ToString("C") +
@"</td></tr>");
                        }
                        if (r[5] != DBNull.Value)
                        {
                            val5 += (Decimal)r[5];
                            sb.Append(@"<tr>
<td>&nbsp; Сумма оптовой реализации в закупочных ценах</td><td>&nbsp;" + ((Decimal)r[5]).ToString("C") +
@"</td></tr>");
                        }
                        if (r[6] != DBNull.Value)
                        {
                            val6 += (Decimal)r[6];
                            sb.Append(@"<tr>
<td>&nbsp; Сумма оптовой реализации</td><td>&nbsp;" + ((Decimal)r[6]).ToString("C") +
@"</td></tr>");
                        }
                    }

                    if (cnt > 0)
                        sb.Append(@"</table></br>");

                    lblShopName.Text = strShopName;
                    oInfo.Text = "";
                    oInfo.Text +=
                    @"
<table border=1>
    <tr>
        <td style='background-color: #505050; color: #00FF00; font-size: large'>
            Значение</td>
        <td style='background-color: #505050; color: #00FF00; font-size: large'>
            Сумма</td>"
                    + @"</tr><tr>
        <td>&nbsp; Сумма реализации</td><td>&nbsp;" + val1.ToString("C") +
                    @"</td></tr>"

                    + @"</tr><tr>
        <td>&nbsp; Сумма проданного товара в закупочных ценах</td><td>&nbsp;" + val2.ToString("C") +
                    @"</td></tr>"

                    + @"</tr><tr>
        <td>&nbsp; Сумма возвратов в закупочных ценах</td><td>&nbsp;" + val3.ToString("C") +
                    @"</td></tr>"

                    + @"</tr><tr>
        <td>&nbsp; Сумма возвратов</td><td>&nbsp;" + val4.ToString("C") +
                    @"</td></tr>"

                    + @"</tr><tr>
        <td>&nbsp; Сумма оптовой реализации в закупочных ценах</td><td>&nbsp;" + val5.ToString("C") +
                    @"</td></tr>"

                    + @"</tr><tr>
        <td>&nbsp; Сумма оптовой реализации</td><td>&nbsp;" + val6.ToString("C") +
                    @"</td></tr>"

                    + @"</table>
";

                    oInfo.Text += sb.ToString();

                }
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
    }

    Exception DBApprovIns(string parShopName, Decimal parAmount, string parApproverLogin, string parApproverName)
    {
        Exception ret = null;

        DateTime dt = oDT.SelectedDate;
        string sql0 =
@"INSERT INTO MoneyConfirm
(D, ShopName, Amount, Approver_Login, Approver_Name) 
VALUES
('{1,2:00}.{0,2:00}.{2,4:0000} 0:00:00', '"
+ parShopName
+ "', "
+ parAmount.ToString().Replace(',', '.')
+ ", '" + parApproverLogin
+ "', '" + parApproverName + "')";
        string sql = string.Format(sql0, dt.Day, dt.Month, dt.Year);

        string connStr = GetAppSetting(@"DB04Connect");

        try
        {
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();
                using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                {
                    int iRowCount = cmd.ExecuteNonQuery();
                    if (iRowCount == 0)
                        throw new Exception("Unknown Error");
                }
            }
        }
        catch (Exception err)
        {
            ret = new Exception(err.Message, err);
        }

        gvAppr.DataBind();

        return ret;
    }

    #endregion Data

}

